package com.database;
import java.sql.*;
import java.util.*;


import com.Helper.*;

public class FlightDB extends Database
{
	private String flightID;
	private String airline = null;
	private String departureLocation = null;
	private String departureTime = null;
	private String arrivalLocation = null;
	private String arrivalTime = null;
	private String status = null;
	private float distance;
	private int capacity;
	private String gate = null;
	private float price;
		
	public FlightDB()
	{
		tableName = "Flight";
	}
	
	public FlightDB(String flightID, String airline, String departureLocation, String departureTime, 
					String arrivalLocation, String arrivalTime, String status, float distance, 
					int capacity, String gate, float price  
					)
	{
		tableName = "Flight";
		
		this.flightID = flightID;
		this.airline = airline;
		this.departureLocation = departureLocation;
		this.departureTime = departureTime;
		this.arrivalLocation = arrivalLocation;
		this.arrivalTime = arrivalTime;
		this.status = status;
		this.distance = distance;
		this.capacity = capacity;	
		this.gate = gate;
		this.price = price;		
	}
	//@requires GetDestList() !== Null;
    //@assignable ArrayList<DestInfo> list,sqlString, Statement, ResultSet;
	//@ensure result == list;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<DestInfo> GetDestList() throws SQLException
	{
		ArrayList<DestInfo> list = new ArrayList<DestInfo>();
		String sqlString = "select distinct arrivalLocation from " + tableName;
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
		{
			DestInfo info = new DestInfo();
			info.arrivalLocation = rs.getString("arrivalLocation");
			list.add(info);
		}
		
		return list;
	}
	

	//@requires GetAllFlights() !== Null;
    //@assignable ArrayList<DestInfo> list,sqlString, Statement, ResultSet;
	//@ensure result == list;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<FlightInfo> GetAllFlights(String date) throws SQLException
	{
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		String sqlString = null;
		
		if (date == null)
		{
			sqlString = "select * from " + tableName;
		}
		else
		{
			String[] s = date.split("-");
			String year = s[0];
			String month = s[1];
			String day = s[2];
			sqlString = "select * from " + tableName + " where YEAR(departureTime) = " + AddQuotes(year)
						+ " and MONTH(departureTime) = " + AddQuotes(month) + 
						" and DAY(departureTime) = " + AddQuotes(day) + " and departureTime > NOW()";
		}
		
		System.out.println(sqlString);
	    Statement stmt = connection.createStatement();
	    
	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())//get flight info
	    {
	    	FlightInfo info = new FlightInfo();
	    	
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);;
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    	
	    	list.add(info);
	    }
	    
	    return list;
	}
	//@requires GetSelectedFlights() !== Null;
    //@assignable flightInfo,sqlString, Statement, ResultSet;
	//@ensure result == list;
	//@signals_only RuntimeException, IOException,SQLException;
	public FlightInfo GetSelectedFlight(String flightID) throws SQLException
	{
		FlightInfo info = new FlightInfo();
		String sqlString = "SELECT * FROM " + tableName + " WHERE flightID = " + AddQuotes(flightID);
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())//get selected flight info
	    {		
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(flightID);
	    }
		
		return info;
	
	}
	
	public ArrayList<FlightInfo> GetFlightByLeavingFrom(String leaving, String date) throws SQLException
	{
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		String sqlString = null;
		
		if (date == null || date.isEmpty())
		{
			sqlString = "SELECT * FROM " + tableName + " where departureLocation = " + AddQuotes(leaving);
		}
		else
		{
			String[] s = date.split("-");
			String year = s[0];
			String month = s[1];
			String day = s[2];
			sqlString = "select * from " + tableName + " where departureLocation = " + AddQuotes(leaving)  
						+ "and YEAR(departureTime) = " + AddQuotes(year)
						+ " and MONTH(departureTime) = " + AddQuotes(month) + 
						" and DAY(departureTime) = " + AddQuotes(day);
		}
		
		
	    Statement stmt = connection.createStatement();
	    
	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())//get flight info
	    {
	    	FlightInfo info = new FlightInfo();
	    	
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);;
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    	
	    	list.add(info);
	    }
	    
	    return list;

	}
	
	public ArrayList<FlightInfo> GetFlightByArrivingAt(String arriving, String date) throws SQLException
	{
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		String sqlString = null;
		
		if (date == null || date.isEmpty())
		{
			sqlString = "SELECT * FROM " + tableName + " where arrivalLocation = " + AddQuotes(arriving);
		}
		else
		{
			String[] s = date.split("-");
			String year = s[0];
			String month = s[1];
			String day = s[2];
			sqlString = "select * from " + tableName + " where arrivalLocation = " + AddQuotes(arriving)  
						+ "and YEAR(arrivalTime) = " + AddQuotes(year)
						+ " and MONTH(arrivalTime) = " + AddQuotes(month) + 
						" and DAY(arrivalTime) = " + AddQuotes(day);
		}
		
	    Statement stmt = connection.createStatement();
	    
	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())
	    {
	    	FlightInfo info = new FlightInfo();
	    	
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);;
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    	
	    	list.add(info);
	    }
	    
	    return list;

	}
	
	public ArrayList<FlightInfo> GetFlightByLeavingArriving(String leaving, String arriving, String date) throws SQLException
	{
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		String sqlString = null;
		
		if (date == null || date.isEmpty())
		{
			sqlString = "SELECT * FROM " + tableName + " where departureLocation = " 
						+ AddQuotes(leaving) + " and arrivalLocation = " + AddQuotes(arriving);
		}
		else
		{
			String[] s = date.split("-");
			String year = s[0];
			String month = s[1];
			String day = s[2];
			sqlString = "SELECT * FROM " + tableName + " where departureLocation = " 
						+ AddQuotes(leaving) + " and arrivalLocation = " + AddQuotes(arriving)
						+ " and YEAR(departureTime) = " + AddQuotes(year) 
						+ " and MONTH(departureTime) = " + AddQuotes(month)
						+ " and DAY(departureTime) = " + AddQuotes(day) 
						+ " and departureTime > NOW()";
		}
		
	    Statement stmt = connection.createStatement();
	    System.out.println(sqlString);
	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())
	    {
	    	FlightInfo info = new FlightInfo();
	    	
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);;
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    	
	    	list.add(info);
	    }
	    
	    return list;

	}
	
	public ArrayList<FlightInfo> GetArrivingFlight(String arriving, String date) throws SQLException
	{
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		String sqlString = null;
		
		if (date == null || date.isEmpty())
		{
			sqlString = "SELECT * FROM " + tableName + " where arrivalLocation = " 
						+ AddQuotes(arriving);
		}
		else
		{
			String[] s = date.split("-");
			String year = s[0];
			String month = s[1];
			String day = s[2];
			sqlString = "SELECT * FROM " + tableName + " where arrivalLocation = " 
						+ AddQuotes(arriving) 
						+ " and YEAR(departureTime) = " + AddQuotes(year) 
						+ " and MONTH(departureTime) = " + AddQuotes(month)
						+ " and DAY(departureTime) = " + AddQuotes(day)
						+ " and departureTime >= NOW()";
		}
		
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
	    	FlightInfo info = new FlightInfo();
	    	
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);;
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    	
	    	list.add(info);
	    }
		
		return list;
	}
	
	public ArrayList<FlightInfo> GetLeavingFlight(String leaving, String date) throws SQLException
	{
		ArrayList<FlightInfo> leavingList = new ArrayList<FlightInfo>();
		String sqlString = null;
		
		if (date == null || date.isEmpty())
		{
			sqlString = "SELECT * FROM " + tableName + " WHERE departureLocation = " + AddQuotes(leaving);
		}
		else
		{
			String[] s = date.split("-");
			String year = s[0];
			String month = s[1];
			String day = s[2];
			sqlString = "SELECT * FROM " + tableName + " where departureLocation = " 
						+ AddQuotes(leaving) 
						+ " and YEAR(departureTime) = " + AddQuotes(year) 
						+ " and MONTH(departureTime) = " + AddQuotes(month)
						+ " and DAY(departureTime) = " + AddQuotes(day)
						+ "and departureTime >= NOW()";
		}
		
		
		
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())
	    {
	    	FlightInfo info = new FlightInfo();
	    	
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);;
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    	
	    	leavingList.add(info);
	    }
		
		return leavingList;
	}
	
	public boolean IsFull(String flightID) throws SQLException
	{
		FlightInfo info = GetSelectedFlight(flightID);
		
		if (info.seatsLeft > 0)
		{
			return false;
		}
		else
		{
			return true;
		}
		
	}
	
	public boolean IsOnTime(String flightID) throws SQLException
	{
		FlightInfo info = new FlightInfo();
		String sqlString = "SELECT * FROM " + tableName + " WHERE flightID = " + AddQuotes(flightID);
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())//get selected flight info
	    {		
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    }
		
		if (info.status.equals("on time"))
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	
	public boolean IsArrived(String flightID) throws SQLException
	{
		FlightInfo info = new FlightInfo();
		String sqlString = "SELECT * FROM " + tableName + " WHERE flightID = " + AddQuotes(flightID);
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())//get selected flight info
	    {		
	    	info.flightID = rs.getString("flightID");
	    	info.airline = rs.getString("airline");
	    	info.departureLocation = rs.getString("departureLocation");
	    	info.departureTime = rs.getString("departureTime").substring(0, 16);
	    	info.arrivalLocation = rs.getString("arrivalLocation");
	    	info.arrivalTime = rs.getString("arrivalTime").substring(0, 16);
	    	info.status = rs.getString("status");
	    	info.distance = rs.getFloat("distance");
	    	info.capacity = rs.getInt("capacity");
	    	info.gate = rs.getString("gate");
	    	info.price = rs.getFloat("price");
	    	info.seatsLeft = GetSeatsLeft(info.flightID);
	    }
		
		if (info.status.equals("arrived"))
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	
	public int GetSeatsLeft(String flightID) throws SQLException
	{
		SeatDB db = new SeatDB();
		db.Setup(connection);
		
		int numReserved = db.GetReservedSeat(flightID).size();
		int capacity = 0;
		
		String sqlString = "SELECT * FROM " + tableName + " WHERE flightID = " + AddQuotes(flightID);
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		
		while (rs.next())//get selected flight info
	    {		
	    	capacity = rs.getInt("capacity");
	    }
			
		// @edward 122 is the max capacity
		int numEmpty = capacity - numReserved;
		
		return numEmpty;
	}
		
	public void MarkArrived(String flightID) throws SQLException
	{
		String sqlString = "update flight set status = 'arrived' where flightID = " + AddQuotes(flightID);	
	    Statement stmt = connection.createStatement();   
	    
	    System.out.println(sqlString);
	    
	    stmt.executeUpdate(sqlString);
	}
	
	public void MarkOnTime(String flightID) throws SQLException
	{
		String sqlString = "update flight set status = 'on time' where flightID = " + AddQuotes(flightID);	
	    Statement stmt = connection.createStatement();   
	    
	    System.out.println(sqlString);
	    
	    stmt.executeUpdate(sqlString);
	}
	
	
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModDepartureLocation(String newLocation, String tupleID) throws SQLException
	{
		super.Modify(newLocation, "departureLocation","flightID",tupleID); 
	}
	//@requires DepartureTime !== Null;
    //@assignable NewTime, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModDepartureTime(String newTime, String tupleID) throws SQLException
	{
		super.Modify(newTime, "departureTime","flightID",tupleID); 
	}
	//@requires ArrivalLocaiton !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModArrivalLocation(String newLocation, String tupleID) throws SQLException
	{
		super.Modify(newLocation, "arrivalLocation","flightID",tupleID); 
	}
	//@requires  ArrivalTime!== Null;
    //@assignable NewTime, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModAppivalTime(String newTime, String tupleID) throws SQLException//<spelling mistake??????????????????????
	{
		super.Modify(newTime, "arrivalTime","flightID",tupleID); 
	}
	//@requires Status !== Null;
    //@assignable NewStatus, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModStatus(String newStatus, String tupleID) throws SQLException
	{
		super.Modify(newStatus, "status","flightID",tupleID); 
	}
	//@requires TotoalSeat !== Null;
    //@assignable NewTotalSeat, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModCapacity(int capacity, String tupleID) throws SQLException
	{
		super.Modify(capacity, "capacity","flightID",tupleID); 
	}
	//@requires Airline !== Null;
    //@assignable newAirline, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModAirline(String newAirline, String tupleID) throws SQLException
	{
		super.Modify(newAirline, "airline","flightID",tupleID); 
	}
	//@requires Distance !== Null;
    //@assignable NewDistance, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void ModDistance(int newDistance, String tupleID) throws SQLException
	{
		super.Modify(newDistance, "distance","flightID",tupleID);
	}
	
	
    //@assignable Statement, sqlString, ArrayList<FlightInfo>list,FlightInfo info1;
	//@ensure result=list
	//@signals_only RuntimeException, IOException,SQLException;	
	
	public ArrayList<FlightInfo> FindFlight(String deploc, String arriloc, int dt1, int dt2, int at1, int at2) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Flight f1 " + " where f1.departureLocation = " + AddQuotes(deploc) + " AND f1.arrivalLocation = " + AddQuotes(arriloc);	
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		FlightInfo info1 = new FlightInfo();
		if (deploc==""&&arriloc!="")
		{
			sqlString = "select * from " + "Flight f1 " + " where f1.arrivalLocation = " + AddQuotes(arriloc);
			System.out.println("didn't input departing location");
		}
		else if (deploc!=""&&arriloc=="")
		{
			sqlString = "select * from " + "Flight f1 " + " where f1.departureLocation = " + AddQuotes(deploc);
			System.out.println("didn't input arrival location");
		}
		ResultSet rs = stmt.executeQuery(sqlString);
		//directing flight
		while (rs.next())
	    {
			int a = Integer.parseInt(rs.getString("f1.departureTime").substring(5,7));
			int b = Integer.parseInt(rs.getString("f1.departureTime").substring(8,10));
			//if ((a==dt1&&b>=dt2)||a>dt2){
			if ((a*100+b)>=(dt1*100+dt2)){
				info1 = GetSelectedFlight(rs.getString("f1.flightID"));
				list.add(info1);
				System.out.println(rs.getString("f1.flightID"));

			}
	    }
		return list;
	}
	//@requires DepartureLocation !== Null;---------------------------------------------------------------------
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<FlightInfo> FindConnectFlight(String deploc, String arriloc, int dt1, int dt2, int at1, int at2) throws SQLException
	{
		Statement stmt = connection.createStatement();
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		FlightInfo info1 = new FlightInfo();
		FlightInfo info2 = new FlightInfo();
		//connecting flight
		String sqlString = "select f2.flightID, f1.flightID,f1.departureTime,f1.arrivalTime,f2.departureTime,f2.arrivalTime from " + "Flight f1,Flight f2 " + " where f1.departureLocation = " + AddQuotes(deploc) + " AND f1.arrivalLocation = f2.departureLocation AND f1.flightID <> f2.flightID AND f2.arrivalLocation = " + AddQuotes(arriloc);
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next())
	    {

			int a = Integer.parseInt(rs.getString("f1.departureTime").substring(5,7));
			int b = Integer.parseInt(rs.getString("f1.departureTime").substring(8,10));
			int c = Integer.parseInt(rs.getString("f1.arrivalTime").substring(5,7));
			int d = Integer.parseInt(rs.getString("f1.arrivalTime").substring(8,10));
			int g = Integer.parseInt(rs.getString("f1.arrivalTime").substring(11,13));
			int e = Integer.parseInt(rs.getString("f2.departureTime").substring(5,7));
			int f = Integer.parseInt(rs.getString("f2.departureTime").substring(8,10));
			int h = Integer.parseInt(rs.getString("f2.departureTime").substring(11,13));
			if (((a*100+b)>=(dt1*100+dt2))&&((c*10000+d*100+g)<=(e*10000+f*100+h))){
				info1 = GetSelectedFlight(rs.getString("f1.flightID"));
				info2 = GetSelectedFlight(rs.getString("f2.flightID"));
				list.add(info1);
				list.add(info2);
				System.out.println(rs.getString("f1.flightID")+ " "+ rs.getString("f2.flightID"));
			}
	    }
		return list;
	}
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<FlightInfo> FindReturnFlight(String deploc, String arriloc, int dt1, int dt2, int at1, int at2) throws SQLException
	{
		Statement stmt = connection.createStatement();
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		FlightInfo info1 = new FlightInfo();
		//returing direct flight
		String sqlString = "select * from " + "Flight f1 " + " where f1.departureLocation = " + AddQuotes(arriloc) + " AND f1.arrivalLocation = " + AddQuotes(deploc);
		System.out.println("returning flight");
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next())
	    {
			int a = Integer.parseInt(rs.getString("f1.departureTime").substring(5,7));
			int b = Integer.parseInt(rs.getString("f1.departureTime").substring(8,10));
			if ((a*100+b)>=(at1*100+at2)){		
				info1 = GetSelectedFlight(rs.getString("f1.flightID"));
				list.add(info1);
				System.out.println(rs.getString("f1.flightID"));

			}
	    }
		return list;
	}
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<FlightInfo> FindReturnConnectFlight(String deploc, String arriloc, int dt1, int dt2, int at1, int at2) throws SQLException
	{
		Statement stmt = connection.createStatement();
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		FlightInfo info1 = new FlightInfo();
		FlightInfo info2 = new FlightInfo();
		//returing connecting flight
		String sqlString = "select f2.flightID, f1.flightID,f1.departureTime,f1.arrivalTime,f2.departureTime,f2.arrivalTime from " + "Flight f1,Flight f2 " + " where f1.departureLocation = " + AddQuotes(arriloc) + " AND f1.arrivalLocation = f2.departureLocation AND f1.flightID <> f2.flightID AND f2.arrivalLocation = " + AddQuotes(deploc);
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next())
	    {
			int a = Integer.parseInt(rs.getString("f1.departureTime").substring(5,7));
			int b = Integer.parseInt(rs.getString("f1.departureTime").substring(8,10));
			int c = Integer.parseInt(rs.getString("f1.arrivalTime").substring(5,7));
			int d = Integer.parseInt(rs.getString("f1.arrivalTime").substring(8,10));
			int g = Integer.parseInt(rs.getString("f1.arrivalTime").substring(11,13));
			int e = Integer.parseInt(rs.getString("f2.departureTime").substring(5,7));
			int f = Integer.parseInt(rs.getString("f2.departureTime").substring(8,10));
			int h = Integer.parseInt(rs.getString("f2.departureTime").substring(11,13));
			if (((a*100+b)>=(at1*100+at2))&&((c*10000+d*100+g)<=(e*10000+f*100+h))){
				info1 = GetSelectedFlight(rs.getString("f1.flightID"));
				info2 = GetSelectedFlight(rs.getString("f2.flightID"));
				list.add(info1);
				list.add(info2);
				System.out.println(rs.getString("f1.flightID")+ " "+ rs.getString("f2.flightID"));
			}
	    }
		
		return list;
	}
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<FlightInfo> FindFlightWithDepartDate( int dt1, int dt2, int at1, int at2) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Flight f1 ";	
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		FlightInfo info1 = new FlightInfo();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		//directing flight
		while (rs.next())
	    {
			int a = Integer.parseInt(rs.getString("f1.departureTime").substring(5,7));
			int b = Integer.parseInt(rs.getString("f1.departureTime").substring(8,10));
			//if ((a==dt1&&b>=dt2)||a>dt2){
			if ((a*100+b)==(dt1*100+dt2)){
				info1 = GetSelectedFlight(rs.getString("f1.flightID"));
				list.add(info1);
				System.out.println(rs.getString("f1.flightID"));

			}
	    }
		return list;
	}
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public ArrayList<FlightInfo> FindFlightWithArrivalDate( int dt1, int dt2, int at1, int at2) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Flight f1 ";	
		ArrayList<FlightInfo> list = new ArrayList<FlightInfo>();
		FlightInfo info1 = new FlightInfo();
		
		ResultSet rs = stmt.executeQuery(sqlString);
		//directing flight
		while (rs.next())
	    {
			int a = Integer.parseInt(rs.getString("f1.departureTime").substring(5,7));
			int b = Integer.parseInt(rs.getString("f1.departureTime").substring(8,10));
			if ((a*100+b)==(at1*100+at2)){		
				info1 = GetSelectedFlight(rs.getString("f1.flightID"));
				list.add(info1);
				System.out.println(rs.getString("f1.flightID"));

			}
	    }
		return list;
	}
	
	
	@Override
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void Insert() throws SQLException 
	{
		Statement stmt = connection.createStatement();
				
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" +
	    					AddQuotes(flightID) + ", " +
	    					AddQuotes(airline) + ", " +
	    					AddQuotes(departureLocation) + ", " +
	    					AddQuotes(departureTime) + ", " +
	    					AddQuotes(arrivalLocation) + ", " +
	    					AddQuotes(arrivalTime) + ", " +
	    					AddQuotes(status) + ", " +
	    					distance + ", " +
	    					capacity + ", " +
	    					AddQuotes(gate) + ", " +
	    					price +
	    					")";
	    System.out.println(sqlString + " complete");
	    stmt.executeUpdate(sqlString);   
	    
	}
	
	@Override
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void Delete(String primaryKey) throws SQLException 
	{
		Statement stmt = connection.createStatement();
		String sqlString = "DELETE from " + tableName + " WHERE flightID = " + AddQuotes(primaryKey);
		stmt.executeUpdate(sqlString);
	}

	@Override
	//@requires DepartureLocation !== Null;
    //@assignable NewLocation, flightID;
	//@signals_only RuntimeException, IOException,SQLException;
	public void Display() throws SQLException 
	{
	    String sqlString = "SELECT * FROM " + tableName;
	    Statement stmt = connection.createStatement();

	    ResultSet rs = stmt.executeQuery(sqlString);

	    while (rs.next())
	    {
	        System.out.println
	        (
	            rs.getString("flightID") + " " +
	            rs.getString("airline") + " " +  
	            rs.getString("departureLocation") + " " +
	            rs.getString("departureTime") + " " +
	            rs.getString("arrivalLocation") + " " +
	            rs.getString("arrivalTime") + " " +  
	            rs.getString("status") + " " +  
	            rs.getString("distance") + " " + 
	            rs.getString("capacity") + " " +            
	            rs.getString("gate") + " " + 
	            rs.getString("price")
	        );
	    }	
	}

}
